package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar2;
import com.bw.bean.Sugar3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT position_sec name,count(*) value FROM user_chapter_process GROUP BY position_sec\n")
    List<Sugar> get1();

    @Select("SELECT tt.question_txt name,a.num1,b.num2,b.num2/a.num1 lv FROM\n" +
            "(SELECT question_id,count(*) num1 FROM test_exam_question GROUP BY question_id) a,\n" +
            "(SELECT question_id,count(*) num2 FROM test_exam_question WHERE is_correct=1 GROUP BY question_id) b,\n" +
            "test_question_info tt \n" +
            "WHERE a.question_id=b.question_id and b.question_id=tt.id")
    List<Sugar2> get2();

    @Select("SELECT p.paper_title name,count(*) num,avg(score) avg1,avg(duration_sec) avg2 FROM test_exam t\n" +
            "INNER JOIN test_paper p ON t.paper_id=p.id\n" +
            "GROUP BY p.paper_title")
    List<Sugar3> get3();

    @Select("SELECT b.source_site name,sum(o.final_amount) value FROM order_info o\n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN video_info v ON d.course_id=v.course_id \n" +
            "INNER JOIN base_source b ON v.video_source_id=b.id\n" +
            "GROUP BY b.source_site ORDER BY sum(o.final_amount) desc")
    List<Sugar> get4();
}
